package cellphone

import (
	"bytes"
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"time"

	"github.com/go-redis/redis/v8"
	"shqsoft.cn/pas3ui/base"
)

var ctx = context.Background()
var keyLock *base.KeyLock = base.NewKeyLock()

var COLNAMES = []string{"ACBL_DGCK", "ACBL_DGDK", "ACBL_CX", "ACBL_GRDK", "ACBL_ZJL"}

func GetApplyAvvl(dateStr string, userID int, tpStr string, rtp string) (avvl float64, err error) {
	var buff bytes.Buffer
	buff.WriteString("with toDate(?) AS EDATE,toStartOfYear(EDATE) AS SDATE,dateDiff('day',SDATE,EDATE)+1 AS DAYS select sum(")
	var tp int
	tp, err = strconv.Atoi(tpStr)
	if tp < 0 || tp > 4 || err != nil {
		err = fmt.Errorf("无效的type:%s", tpStr)
		return
	}
	buff.WriteString(COLNAMES[tp])
	buff.WriteString(")/DAYS AVVL from DFKB_EMPL_INDEX where DTID>=SDATE and DTID<=EDATE")
	// if tp == 0 || tp == 1 {
	// 	buff.WriteString(" AND CYNO='01'")
	// }
	if rtp == "2" {
		buff.WriteString(" and EMID=")
		buff.WriteString(strconv.Itoa(userID))
	} else if rtp == "1" {
		buff.WriteString(" and EMID in (with dictGetUInt32('DICT_USER_DEPT','PDID',toUInt64(")
		buff.WriteString(strconv.Itoa(userID))
		buff.WriteString(")) AS PDID2 select toUInt32(EMID) from DICT_USER_DEPT where PDID =PDID2)")
	}
	row := base.CHDB.Raw(buff.String(), dateStr).Row()
	err = row.Scan(&avvl)
	return
}

type Ranking struct {
	Sequence                       uint32  `json:"sequence"`                       //Number   | 序号
	Name                           string  `json:"name"`                           //String   | 排行名称（各支行)
	AsverageDaily                  float64 `json:"averageDaily"`                   //Number   | 排行日均（各支行)
	TimepointBalance               float64 `json:"timepointBalance"`               //Number   | 时点余额（各支行）
	YearTimepointIncrement         float64 `json:"yearTimepointIncrement"`         //Number   | 较年初时点增量（各支行）
	LastMonthAverageDailyIncrement float64 `json:"lastMonthAverageDailyIncrement"` //Number   | 较上月日均增量 (各支行）
	YearAverageDailyIncrement      float64 `json:"yearAverageDailyIncrement"`      //Number   | 较年初日均增量（各支行）
}

func GetApplyRankingList(dateStr string, tpStr string) (rkl []Ranking, err error) {
	var tp int
	tp, err = strconv.Atoi(tpStr)
	if tp < 0 || tp > 4 || err != nil {
		err = fmt.Errorf("无效的type:%s", tpStr)
		return
	}
	var buff bytes.Buffer
	buff.WriteString("with toDate(?) AS EDATE,toStartOfYear(EDATE) AS SDATE,dateDiff('day',SDATE,EDATE)+1 AS DAYS,")
	buff.WriteString("dateAdd(DAY,-1,toStartOfMonth(EDATE)) AS SYEDATE,dateDiff('day',SDATE,SYEDATE)+1 AS SYDAYS,")
	buff.WriteString("dateAdd(YEAR,-1,SDATE) AS QNSDATE,dateAdd(DAY,-1,SDATE) AS QNEDATE,dateDiff('day',QNSDATE,QNEDATE)+1 AS QNDAYS ")
	buff.WriteString(fmt.Sprintf("select sumIf(%s,DTID>=SDATE)/DAYS AS AVVL,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("sumIf(%s,DTID=EDATE) AS ACBL,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("ACBL-sumIf(%s,DTID=QNEDATE) AS ACBL_JNC,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("AVVL-sumIf(%s,DTID<=QNEDATE)/QNDAYS AS AVVL_JNC,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("if(toMonth(EDATE)=1,AVVL_JNC,AVVL-sumIf(%s,DTID>=SDATE and DTID<=SYEDATE)/SYDAYS) AS AVVL_JSY,", COLNAMES[tp]))
	buff.WriteString("dictGetString('DICT_USER_DEPT','PDNM',toUInt64(EMID)) AS PDNM")
	buff.WriteString(" from DFKB_EMPL_INDEX where DTID>=QNSDATE and DTID<=EDATE")
	// if tp == 0 || tp == 1 {
	// 	buff.WriteString(" AND CYNO='01'")
	// }
	buff.WriteString(" group by PDNM order by AVVL desc")
	var rows *sql.Rows
	rows, err = base.CHDB.Raw(buff.String(), dateStr).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	rkl = make([]Ranking, 0, 30)
	var i uint32 = 1
	for rows.Next() {
		rk := Ranking{Sequence: i}
		err = rows.Scan(&rk.AsverageDaily, &rk.TimepointBalance, &rk.YearTimepointIncrement, &rk.YearAverageDailyIncrement, &rk.LastMonthAverageDailyIncrement, &rk.Name)
		if err != nil {
			return
		}
		rkl = append(rkl, rk)
		i++
	}
	return
}

type Manager struct {
	Sequence     uint32  `json:"sequence"`     //Number   | 序号
	Name         string  `json:"name"`         //String   | 排行名称（客户经理)
	RoleType     string  `json:"roleType"`     //String   | 排行名称角色| 0： 综合员,1：零售,2：对公
	AverageDaily float64 `json:"averageDaily"` //Number   | 日均（万元)
	Balance      float64 `json:"balance"`      //Number   | 余额（万元)
}

func GetManagerList(dateStr string, userID int, tpStr string) (ml []Manager, err error) {
	var hkey = "user:serial"
	var count int64
	count, err = base.RedisCli.Exists(ctx, hkey).Result()
	if err != nil {
		return
	}
	if count == 0 {
		err = createUserSerial()
		if err != nil {
			return
		}
	}
	var tp int
	tp, err = strconv.Atoi(tpStr)
	if tp < 0 || tp > 4 || err != nil {
		err = fmt.Errorf("无效的type:%s", tpStr)
		return
	}
	var buff bytes.Buffer
	buff.WriteString("with toDate(?) AS EDATE,toStartOfYear(EDATE) AS SDATE,dateDiff('day',SDATE,EDATE)+1 AS DAYS ")
	buff.WriteString(fmt.Sprintf("select EMID,sum(%s)/DAYS AS AVVL,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("sumIf(%s,DTID=EDATE) AS ACBL,", COLNAMES[tp]))
	buff.WriteString("dictGetString('DICT_USER_DEPT','EMNM',toUInt64(EMID)) AS EMNM")
	buff.WriteString(" from DFKB_EMPL_INDEX where DTID>=SDATE and DTID<=EDATE")
	// if tp == 0 || tp == 1 {
	// 	buff.WriteString(" AND CYNO='01'")
	// }
	buff.WriteString(" and EMID in (with dictGetUInt32('DICT_USER_DEPT','PDID',toUInt64(")
	buff.WriteString(strconv.Itoa(userID))
	buff.WriteString(")) AS PDID2 select toUInt32(EMID) from DICT_USER_DEPT where PDID =PDID2)")
	buff.WriteString(" group by EMID order by AVVL desc")
	var rows *sql.Rows
	rows, err = base.CHDB.Raw(buff.String(), dateStr).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	ml = make([]Manager, 0, 30)
	roids := []int{1, 4, 5}
	if tp < 2 {
		roids = []int{1, 4}
	} else if tp < 4 {
		roids = []int{1, 5}
	}
	var i uint32 = 1
	for rows.Next() {
		var emid int
		m := Manager{Sequence: i}
		err = rows.Scan(&emid, &m.AverageDaily, &m.Balance, &m.Name)
		if err != nil {
			return
		}
		var serialStr string
		var serial int
		serialStr, _ = base.RedisCli.HGet(ctx, hkey, strconv.Itoa(emid)).Result()
		if serialStr == "" {
			continue
		}
		serial, err = strconv.Atoi(serialStr)
		if err != nil {
			return
		}
		if !base.IsInSliceForInt(roids, serial) {
			continue
		}
		m.RoleType = serialStr
		i++
		ml = append(ml, m)
	}
	return
}

type EmplData struct {
	YearTimepointIncrementType         string  `json:"yearTimepointIncrementType"`         //String   | 较年初时点增量类型 | 0：上升1：下降
	YearAverageDailyIncrementType      string  `json:"yearAverageDailyIncrementType"`      //String   | 较年初日均增量类型 | 0：上升1：下降
	LastMonthAverageDailyIncrementType string  `json:"lastMonthAverageDailyIncrementType"` //String   | 较上月日均增量类型| 0：上升1：下降
	AverageDaily                       float64 `json:"averageDaily"`                       //String   | 日均
	TimepointBalance                   float64 `json:"timepointBalance"`                   //Number   | 时点余额
	YearTimepointIncrement             float64 `json:"yearTimepointIncrement"`             //Number   | 较年初时点增量
	LastMonthAverageDailyIncrement     float64 `json:"lastMonthAverageDailyIncrement"`     //Number   | 较上月日均增量
	YearAverageDailyIncrement          float64 `json:"yearAverageDailyIncrement"`          //Number   | 较年初日均增量
	Ranking                            uint16  `json:"ranking"`                            //Number   | 排名，后五名为1,2,3,4,5，其它为0
}

func GetEmplData(dateStr string, userID int, tpStr string) (ed EmplData, err error) {
	var tp int
	tp, err = strconv.Atoi(tpStr)
	if tp < 0 || tp > 4 || err != nil {
		err = fmt.Errorf("无效的type:%s", tpStr)
		return
	}
	var buff bytes.Buffer
	buff.WriteString("with toDate(?) AS EDATE,toStartOfYear(EDATE) AS SDATE,dateDiff('day',SDATE,EDATE)+1 AS DAYS,")
	buff.WriteString("dateAdd(DAY,-1,toStartOfMonth(EDATE)) AS SYEDATE,dateDiff('day',SDATE,SYEDATE)+1 AS SYDAYS,")
	buff.WriteString("dateAdd(YEAR,-1,SDATE) AS QNSDATE,dateAdd(DAY,-1,SDATE) AS QNEDATE,dateDiff('day',QNSDATE,QNEDATE)+1 AS QNDAYS ")
	buff.WriteString(fmt.Sprintf("select sumIf(%s,DTID>=SDATE)/DAYS AS AVVL,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("sumIf(%s,DTID=EDATE) AS ACBL,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("ACBL-sumIf(%s,DTID=QNEDATE) AS ACBL_JNC,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("AVVL-sumIf(%s,DTID<=QNEDATE)/QNDAYS AS AVVL_JNC,", COLNAMES[tp]))
	buff.WriteString(fmt.Sprintf("if(toMonth(EDATE)=1,AVVL_JNC,AVVL-sumIf(%s,DTID>=SDATE and DTID<=SYEDATE)/SYDAYS) AS AVVL_JSY,", COLNAMES[tp]))
	buff.WriteString("if(ACBL_JNC>0,'1','0') LX1,if(AVVL_JNC>0,'1','0') LX2,if(AVVL_JSY>0,'1','0') LX3")
	buff.WriteString(" from DFKB_EMPL_INDEX where DTID>=QNSDATE and DTID<=EDATE and EMID=?")
	// if tp == 0 || tp == 1 {
	// 	buff.WriteString(" AND CYNO='01'")
	// }
	row := base.CHDB.Raw(buff.String(), dateStr, userID).Row()
	err = row.Scan(&ed.AverageDaily, &ed.TimepointBalance, &ed.YearTimepointIncrement, &ed.YearAverageDailyIncrement, &ed.LastMonthAverageDailyIncrement,
		&ed.YearTimepointIncrementType, &ed.YearAverageDailyIncrementType, &ed.LastMonthAverageDailyIncrementType)
	if err != nil {
		return
	}
	ed.Ranking, err = getUserRanking(dateStr, userID, tp)
	return
}

func getUserRanking(dateStr string, userID int, tp int) (ranking uint16, err error) {
	sqlStr := "select PDID from DICT_USER_DEPT where EMID=?"
	var utid int
	row := base.CHDB.Raw(sqlStr, userID).Row()
	err = row.Scan(&utid)
	if err != nil {
		return
	}
	var zkey = fmt.Sprintf("ranking:%s|%d|%d", dateStr, utid, tp)
	var count int64
	count, err = base.RedisCli.Exists(ctx, zkey).Result()
	if err != nil {
		return
	}
	if count == 0 {
		err = createUserRanking(dateStr, utid, tp)
		if err != nil {
			return
		}
	}
	emid := strconv.Itoa(userID)
	var score float64
	score, err = base.RedisCli.ZScore(ctx, zkey, emid).Result()
	if err != nil {
		return 0, nil
	}
	if score == 0 {
		return 1, nil
	}
	var emids []string
	emids, err = base.RedisCli.ZRange(ctx, zkey, 0, 5).Result()
	if err != nil {
		return
	}
	i := 1
	for _, id := range emids {
		if emid == id {
			return uint16(i + 1), nil
		}
	}
	ranking = 0
	return
}
func createUserSerial() (err error) {
	var hkey = "user:serial"
	keyLock.Lock(hkey)
	defer keyLock.Unlock(hkey)
	var count int64
	count, err = base.RedisCli.Exists(ctx, hkey).Result()
	if err != nil {
		return
	}
	if count > 0 {
		return
	}
	sqlStr := `select "ID","Serial" from "T_SysUser" where "Serial" in (1,4,5)`
	var rows *sql.Rows
	rows, err = base.MYDB.Raw(sqlStr).Rows()
	infoMap := make(map[string]interface{})
	for rows.Next() {
		var emid int
		var serial int
		rows.Scan(&emid, &serial)
		infoMap[strconv.Itoa(emid)] = serial
	}
	err = base.RedisCli.HMSet(ctx, hkey, infoMap).Err()
	if err != nil {
		return
	}
	err = base.RedisCli.Expire(ctx, hkey, time.Hour*12).Err()
	return
}

func createUserRanking(dateStr string, utid int, tp int) (err error) {
	var zkey = fmt.Sprintf("ranking:%s|%d|%d", dateStr, utid, tp)
	keyLock.Lock(zkey)
	defer keyLock.Unlock(zkey)
	var count int64
	count, err = base.RedisCli.Exists(ctx, zkey).Result()
	if err != nil {
		return
	}
	if count > 0 {
		return
	}
	sqlStr := fmt.Sprintf(`with toDate(?) AS EDATE,toStartOfYear(EDATE) AS SDATE,dateDiff('day',SDATE,EDATE)+1 AS DAYS
	select EMID,sum(%s)/DAYS AVVL
	from DFKB_EMPL_INDEX
	where DTID>=SDATE and DTID<=EDATE and
		  EMID in (select toUInt32(EMID) from DICT_USER_DEPT where PDID=? and EMID in (select ID from mysql.T_SysUser where Serial in (1,4,5)))
	group by EMID`, COLNAMES[tp])
	var rows *sql.Rows
	rows, err = base.CHDB.Raw(sqlStr, dateStr, utid).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	zdatas := make([]*redis.Z, 0, 20)
	for rows.Next() {
		var emid int
		var avvl float64
		rows.Scan(&emid, &avvl)
		zdatas = append(zdatas, &redis.Z{Score: avvl, Member: emid})
	}
	if len(zdatas) == 0 {
		zdatas = append(zdatas, &redis.Z{Score: 0.0, Member: 0})
	}
	err = base.RedisCli.ZAdd(ctx, zkey, zdatas...).Err()
	if err != nil {
		return
	}
	err = base.RedisCli.Expire(ctx, zkey, time.Hour*12).Err()
	return
}
